package com.packtpub.learningcypher.chapter5.sql;

import java.sql.*;
import java.util.Random;

/**
 * This class sets up a new SQL database (DERBY)
 * 
 * @author Onofrio Panzarino
 */
public class SqlDatabaseSetup {

    public String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    public String protocol = "jdbc:derby:";

    public SqlReferenceRepository setup() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Class.forName(driver).newInstance();

        Connection conn = DriverManager.getConnection(protocol + "database/SqlDb;create=true");

        createSchema(conn);
        return new SqlReferenceRepository(conn);
    }

    private void createSchema(Connection conn) throws SQLException {
        execute(conn,
                "CREATE TABLE PUBLISHERS(NAME VARCHAR(200) NOT NULL PRIMARY KEY, Details VARCHAR(3000))");
        execute(conn,
                "CREATE TABLE REFERENCEENTRIES("
                + "ID int NOT NULL  PRIMARY KEY "
                + "GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                + "TITLE VARCHAR(200) NOT NULL, "
                + "ABSTRACTTEXT VARCHAR(1000) NOT NULL, "
                + "PUBLISHEDBY VARCHAR(200) REFERENCES PUBLISHERS(NAME) ON DELETE SET NULL ON UPDATE RESTRICT,"
                + "PublishedYear int)");
        execute(conn,
                "CREATE TABLE EntryTags("
                + "ReferenceId int REFERENCES ReferenceEntries(ID),"
                + "Tag VARCHAR(50))");
        execute(conn,
                "CREATE TABLE AUTHORS(ID int NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                        + "NAME VARCHAR(200) NOT NULL, SURNAME VARCHAR(200) NOT NULL)");
        execute(conn,
                "CREATE TABLE ReferenceAuthors("
                + "ReferenceId int REFERENCES ReferenceEntries(ID),"
                + "AuthorId int REFERENCES Authors(ID),"
                + "SortOrder int)");
        execute(conn,
                "CREATE TABLE ReferenceCitations("
                + "ReferenceId int REFERENCES ReferenceEntries(ID),"
                + "CitationId int REFERENCES ReferenceEntries(ID))");
        
        fillData(conn);
    }

    private void fillData(Connection conn) throws SQLException {
        for (int i = 1; i < 100; i++) {
            execute(conn, "INSERT INTO Publishers(name) values('Publisher " + i + "')");
            execute(conn, "INSERT INTO Authors(name, surname) values('Name " + i + "', 'Surname " + i + "')");
        }
        
        Random random = new Random();
        
        for (int i = 1; i < 100; i++) {
            execute(conn, 
                    "INSERT INTO REFERENCEENTRIES(TITLE, ABSTRACTTEXT, PublishedBy, PublishedYear) "
                  + "VALUES ('Title " + i + "', 'ABSTRACT', 'Publisher " + i + "' ," + (1900+i) + ")");
            execute(conn,
                    "INSERT INTO ReferenceAuthors values(" + i + "," + i + ", 1)");
            
            if(i > 1)
                execute(conn,
                    "INSERT INTO ReferenceAuthors values(" + i + "," + (i-1) + ", 1)");
     
            execute(conn,
                    "INSERT INTO EntryTags values(" + i + ", 'TAG " + (random.nextInt(4)) + "')");
            execute(conn,
                    "INSERT INTO EntryTags values(" + i + ", 'TAG " + (random.nextInt(7)) + "')");
            execute(conn,
                    "INSERT INTO EntryTags values(" + i + ", 'TAG " + (random.nextInt(9)) + "')");
            execute(conn,
                    "INSERT INTO EntryTags values(" + i + ", 'TAG " + (random.nextInt(11)) + "')");
        }
        
        for (int i = 1; i < 300; i++) {
            execute(conn,
                    "INSERT INTO ReferenceCitations values(" + (random.nextInt(50)+1) + "," + (random.nextInt(99)+1) + ")");
        }
    }

    private void execute(Connection conn, String sql) throws SQLException {
        final Statement statement = conn.createStatement();
        statement.execute(sql);
    }
}
